package com.it135.citytraffic.mapper;

import com.it135.citytraffic.pojo.entity.TrafficInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.it135.citytraffic.pojo.vo.TrafficInfoAverageVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;

/**
 * <p>
 * 路口交通情况信息表 Mapper 接口
 * </p>
 *
 *
 */
@Mapper
public interface TrafficInfoMapper extends BaseMapper<TrafficInfo> {

    /**
     * bl
     * 获取平均数据
     *
     * @param beginDate
     * @param endDate
     * @param beginTime
     * @param endTime
     * @return
     */
    @Select("SELECT ROUND(avg(REPLACE(blocked, '%', '')),2) as blockedAvg ,ROUND(avg(REPLACE(expedite, '%', '') ),2)  as expediteAvg \n" +
            "     ,ROUND(avg(REPLACE(congested, '%', '')),2)  as congestedAvg ,ROUND(avg(status),2)  as statusAvg \n" +
            "FROM traffic_info\n" +
            "WHERE DATE(create_time) BETWEEN #{beginDate} AND #{endDate}\n" +
            "  AND TIME(create_time) BETWEEN #{beginTime} AND #{endTime};")
    TrafficInfoAverageVO getInfoAvg(LocalDate beginDate, LocalDate endDate, LocalTime beginTime, LocalTime endTime);
    @Select("SELECT ROUND(avg(REPLACE(blocked, '%', '')),2) as blockedAvg ,ROUND(avg(REPLACE(expedite, '%', '') ),2)  as expediteAvg \n" +
            "     ,ROUND(avg(REPLACE(congested, '%', '')),2)  as congestedAvg \n" +
            "FROM traffic_info\n" +
            "WHERE  create_time between #{startOfDay} and #{endOfDay} and crossing_id = #{crossingId};" )
    TrafficInfoAverageVO getAvgByCrossingId(Integer crossingId, LocalDateTime startOfDay, LocalDateTime endOfDay);
 }
